﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Data.SqlClient;
using System.IO;

public partial class A_CountPage : System.Web.UI.Page
{
    public static DataTable ta = new DataTable();//用于存储从数据库中取出来的表格，可以在多个函数中使用，所以使用static
    public static DataView vi = new DataView();  //用于数据绑定

    protected void Page_Load(object sender, EventArgs e)
    {
        btnFirst.Text = "最首页";    //分页处理
        btnPrev.Text = "前一页";
        btnNext.Text = "下一页";
        btnLast.Text = "最后页";

        if (!IsPostBack)
        {
            HideDataList();
            DL1.Text = "请选择学期";
            DL2.Text = "请选择统计方式";
            Label1.Text = "输入信息";
            TextBox1.Text = "";
        }
    }

    void ExecuteSQL(string sql)
    {
        //string sql = "select courseid,COURSE_INFO.name as coursename,studentid,STU_INFO.name as studentname,STU_INFO.class as class,mark,SEM_INFO.id as semesterid,SEM_INFO.name as semester from SEM_INFO,CLASS_INFO,COURSE_INFO,STU_INFO where CLASS_INFO.courseid=COURSE_INFO.id and CLASS_INFO.studentid=STU_INFO.id and CLASS_INFO.semesterid=SEM_INFO.id and CLASS_INFO.state='1' order by STU_INFO.id ;";
        SqlConnection myConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=CSDB");
        SqlCommand cmd = new SqlCommand(sql, myConn);
        myConn.Open();

        SqlDataAdapter da = new SqlDataAdapter();
        da.SelectCommand = cmd;
        ta.Columns.Clear();
        ta.Rows.Clear();
        da.Fill(ta);
        vi = new DataView(ta);
        dg1.DataSource = vi;
        dg1.DataBind();
        myConn.Close();
    }
    void Execute(string sql)
    {
        SqlConnection myConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=CSDB");
        SqlCommand cmd = new SqlCommand(sql, myConn);
        myConn.Open();
        cmd.ExecuteNonQuery();
        myConn.Close();
    }

    void ShowDataList()
    {
        dg1.Visible = true;
        btnFirst.Visible = true;
        btnPrev.Visible = true;
        btnNext.Visible = true;
        btnLast.Visible = true;
        lblCurrentIndex.Visible = true;
        lblPageCount.Visible = true;
    }
    void HideDataList()
    {
        dg1.Visible = false;
        btnFirst.Visible = false;
        btnPrev.Visible = false;
        btnNext.Visible = false;
        btnLast.Visible = false;
        lblCurrentIndex.Visible = false;
        lblPageCount.Visible = false;
    }

    private void ShowStats()  //用于分页处理
    {
        lblCurrentIndex.Text = "第 " + (dg1.CurrentPageIndex + 1).ToString() + " 页";
        lblPageCount.Text = "总共 " + dg1.PageCount.ToString() + " 页";
    }

    public void PagerButtonClick(object sender, EventArgs e) //用于分页处理
    {
        string arg = ((LinkButton)sender).CommandArgument.ToString();
        switch (arg)
        {
            case "next":
                if (dg1.CurrentPageIndex < (dg1.PageCount - 1))
                {
                    dg1.CurrentPageIndex += 1;
                }
                break;
            case "prev":
                if (dg1.CurrentPageIndex > 0)
                {
                    dg1.CurrentPageIndex -= 1;
                }
                break;
            case "last":
                dg1.CurrentPageIndex = (dg1.PageCount - 1);
                break;
            default:
                dg1.CurrentPageIndex = System.Convert.ToInt32(arg);
                break;
        }
        vi = new DataView(ta);

        dg1.DataSource = vi;
        dg1.DataBind();
        ShowStats();
    }

    public void MyDataGrid_Page(Object source, DataGridPageChangedEventArgs e)  //用于分页处理
    {
        int startIndex;
        startIndex = dg1.CurrentPageIndex * dg1.PageSize;
        dg1.CurrentPageIndex = e.NewPageIndex;
        //BindGrid();
        vi = new DataView(ta);

        dg1.DataSource = vi;
        dg1.DataBind();
        ShowStats();

    }

    protected void LinkButton2_Click(object sender, EventArgs e)
    {
        HideDataList();
        DL1.Text = "请选择学期";
        DL2.Text = "请选择统计方式";
        Label1.Text = "输入信息";
        TextBox1.Text = "";
    }
    protected void DL2_TextChanged(object sender, EventArgs e)
    {

        if (DL2.Text == "请选择统计方式")
        {
            Label1.Text = "输入信息";
        }
        else if (DL2.Text == "按职工号统计")
        {
            Label1.Text = "请输入职工号";
        }
        else if (DL2.Text == "按课程名统计")
        {
            Label1.Text = "请输入课程名";

        }

    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        string sql = "";
        if (DL1.Text == "请选择学期")
        {
            Alert("请选择学期");
        }
        else
        {
            if (DL2.Text == "请选择统计方式")
            {
                Alert("请选择统计方式");
            }
            else
            {   
                ShowDataList();
                if (DL2.Text == "按职工号统计")
                {
                    sql = "alter view tea_mark as select COURSE_INFO.id as courseid,COURSE_INFO.name as coursename,TEA_INFO.id as teacherid,TEA_INFO.name as teachername,mark,SEM_INFO.name as semester from CLASS_INFO ,TEA_INFO,COURSE_INFO,SEM_INFO where COURSE_INFO.teacherid=TEA_INFO.id and TEA_INFO.id='" + TextBox1.Text + "' and CLASS_INFO.courseid=COURSE_INFO.id and CLASS_INFO.state='1' and CLASS_INFO.semesterid=SEM_INFO.id and SEM_INFO.name='" + DL1.Text + "';";
                    Execute(sql);
                    sql = "alter view avg_mark as select courseid,max(mark)as highmark,min(mark)as lowmark,avg(mark)as avgmark from tea_mark group by courseid;";
                    Execute(sql);
                    sql = "select distinct tea_mark.courseid,coursename,teacherid,teachername,avg_mark.highmark,avg_mark.lowmark,avg_mark.avgmark,semester from tea_mark,avg_mark where tea_mark.courseid=avg_mark.courseid;";
                    Execute(sql);
                }
                else if (DL2.Text == "按课程名统计")
                {
                    sql = "alter view course_mark as select COURSE_INFO.id as courseid,COURSE_INFO.name as coursename,TEA_INFO.id as teacherid,TEA_INFO.name as teachername,mark,SEM_INFO.name as semester from CLASS_INFO ,TEA_INFO,COURSE_INFO,SEM_INFO where COURSE_INFO.teacherid=TEA_INFO.id and COURSE_INFO.name='"+TextBox1.Text+"' and CLASS_INFO.courseid=COURSE_INFO.id and CLASS_INFO.state='1' and CLASS_INFO.semesterid=SEM_INFO.id and SEM_INFO.name='"+DL1.Text+"';";
                    Execute(sql);
                    sql = "alter view avg_mark as select max(mark)as highmark,min(mark)as lowmark,avg(mark)as avgmark from tea_mark;";
                    Execute(sql);
                    sql = "select distinct course_mark.courseid,coursename,teacherid,teachername,avg_mark.highmark,avg_mark.lowmark,avg_mark.avgmark,semester from course_mark,avg_mark;";
                    Execute(sql);
                }
                ExecuteSQL(sql);
            }
        }

    }
    void Alert(string message)//用于测试，无实际意义
    {
        Response.Write("<script>alert('" + message + "')</script>");
    }
}
